<?php

class JournalTable extends Doctrine_Table
{
	public function getLastInsert(){
		$q=Doctrine_Query::create()
		  -> select('j.id')
		  -> from('journal AS j')
		  -> orderby('j.id DESC')
		  -> limit('1');
		  
		return $q->execute();
	}
	// on reccup�re le journal en cours
	public function getJournalEncours($max=1){
		$q= Doctrine_Query::create()
		  -> select('j.id as idJournal,j.etat,j.titre as titreJournal,j.resume as resJournal,j.dateapparution as dateSortie,j.docjoint as document, ja.*, a.titre as articleTitre, a.resume, a.dateexpiration, r.titre as rubriqueTitre')
		  -> from('journal j')
		  -> innerJoin('j.JournalArticle ja')
		  -> innerJoin('ja.Article a')
		  -> innerJoin('a.Rubrique r')
		  -> where('j.dateapparution <?', date('Y-m-d H:i:s'))
		  -> andWhere('j.etat =?',1)
		  //-> andWhere('a.dateexpiration >?', time())
		  -> andWhere('a.etat =?',1)
		  -> andWhere('r.etat =?',1)
		  -> orderby ('j.id DESC')
		  -> limit($max);
		  
		//echo $q->getSqlQuery(); die();
		return $q->execute();
	}
	
	public function getJournalArchives(){
		$q = Doctrine_Query::create()
		  -> select('j.*')
		  -> from('journal j')
		  -> where('j.etat=?',1);
		 // -> limit($max);
		
		return $q->execute();
	}
	
	public function getLatestNews(){
		$q = Doctrine_Query::create()
		  -> from('Journal j')
		  -> where('j.etat=',1)
		  -> orderBy('j.create_at desc')
		  -> limit(1);
		
		return $q->fetchOne();
	}
  
	public function activeDeactiveJournal($valeur,$etat){
		$q = Doctrine_Query::create()
		  -> update('journal')
		  -> set('etat=?',$etat)
		  -> where('id =?', $valeur);
		  
		return $q->execute();
	}
	
	static public function getLuceneIndex(){
		ProjectConfiguration::registerZend();
		if (file_exists($index = self::getLuceneIndexFile())){
			return Zend_Search_Lucene::open($index);
		}
		else{
			return Zend_Search_Lucene::create($index);
		}
	}
	
	static public function getLuceneIndexFile(){
		return sfConfig::get('sf_data_dir').'/journal.'.sfConfig::get('sf_environment').'.index';
	}
	
	public function getForLuceneQuery($query){
		$hits = $this->getLuceneIndex()->find($query);
		$pks = array();
		foreach($hits as $hit){
			$pks[] = $hit->pk;
		}
		//print_r($pks);die();
		if(empty($pks)){
			return array();
		}
		$q = Doctrine_Query::create()
		   -> select('j.*')
		   -> from('journal j')
		   -> whereIn('j.id',$pks)
		   -> andWhere('j.etat=?',1);
		   
		//echo $q->getSqlQuery(); die();
		return $q->execute();
	}
        
        //rechercher un journal
        public function getJournalById($id){
            $q= Doctrine_Query::create()
		  -> select('j.id as idJournal,j.etat,j.titre as titreJournal,j.resume as resJournal,j.dateapparution as dateSortie,j.docjoint as document, ja.*, a.titre as articleTitre, a.resume, a.dateexpiration, r.titre as rubriqueTitre')
		  -> from('journal j')
		  -> innerJoin('j.JournalArticle ja')
		  -> innerJoin('ja.Article a')
		  -> innerJoin('a.Rubrique r')
		  -> where('j.id =?', intval($id))
		  -> andWhere('j.etat =?',1)
		  -> andWhere('a.etat =?',1)
		  -> andWhere('r.etat =?',1)
		  -> orderby ('ja.ordre ASC');
		  
		//echo $q->getSqlQuery(); die();
		return $q->execute();
        }
        public function getArticlesJ($id){
            $q= Doctrine_Query::create()
		  -> select('j.id as idJournal,j.etat,j.titre as titreJournal, ja.*, a.titre as articleTitre')
		  -> from('journal j')
		  -> innerJoin('j.JournalArticle ja')
		  -> innerJoin('ja.Article a')
		  -> where('ja.journalid =?', intval($id))
		  -> orderby ('ja.ordre ASC');
		  
		 // echo $q->getSqlQuery(); die();
		return $q->execute();
        }
}